/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2008-10-30 9:06:40                           */
/*==============================================================*/


/*==============================================================*/
/* Table: EmailSentQueue                                        */
/*==============================================================*/
create table dbo.EmailSentQueue (
   ID                   int                  identity,
   MailType             tinyint              not null,
   Recipient            varchar(128)         not null,
   Subject              varchar(256)         not null,
   Content              ntext                not null,
   SentDate             datetime             not null,
   Status               int                  not null,
   constraint PK_EMAILSENTQUEUE primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Sent mails',
   'user', 'dbo', 'table', 'EmailSentQueue'
go

execute sp_addextendedproperty 'MS_Description', 
   'It will be a enumerator (undetermined, it depends on the mail system)',
   'user', 'dbo', 'table', 'EmailSentQueue', 'column', 'MailType'
go

execute sp_addextendedproperty 'MS_Description', 
   'Send to',
   'user', 'dbo', 'table', 'EmailSentQueue', 'column', 'Recipient'
go

execute sp_addextendedproperty 'MS_Description', 
   '0 for success or error code',
   'user', 'dbo', 'table', 'EmailSentQueue', 'column', 'Status'
go

/*==============================================================*/
/* Table: EmailWaitQueue                                        */
/*==============================================================*/
create table dbo.EmailWaitQueue (
   ID                   int                  identity,
   MailType             tinyint              not null,
   Recipient            varchar(128)         not null,
   Priority             tinyint              not null,
   Subject              varchar(256)         not null,
   Content              ntext                not null,
   CreateDate           datetime             not null,
   Retries              tinyint              not null,
   SentDate             datetime             null,
   constraint PK_EMAILWAITQUEUE primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'The mails waiting to be send, delete when sent',
   'user', 'dbo', 'table', 'EmailWaitQueue'
go

execute sp_addextendedproperty 'MS_Description', 
   'It will be a enumerator (undetermined, it depends on the mail system)',
   'user', 'dbo', 'table', 'EmailWaitQueue', 'column', 'MailType'
go

execute sp_addextendedproperty 'MS_Description', 
   'Send to',
   'user', 'dbo', 'table', 'EmailWaitQueue', 'column', 'Recipient'
go

execute sp_addextendedproperty 'MS_Description', 
   'Smaller for more urgent, verify mail for register will be 0',
   'user', 'dbo', 'table', 'EmailWaitQueue', 'column', 'Priority'
go

execute sp_addextendedproperty 'MS_Description', 
   'Number of retries, the max number is setting by mail system',
   'user', 'dbo', 'table', 'EmailWaitQueue', 'column', 'Retries'
go

execute sp_addextendedproperty 'MS_Description', 
   'Used for retry, update this field if sent failed',
   'user', 'dbo', 'table', 'EmailWaitQueue', 'column', 'SentDate'
go

/*==============================================================*/
/* Table: FriendGroup                                           */
/*==============================================================*/
create table dbo.FriendGroup (
   ID                   int                  identity,
   UserId               int                  not null,
   GroupName            varchar(64)          not null,
   constraint PK_FRIENDGROUP primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Friend group, include default groups and custom groups, the default groups as same as custom groups (be able to edit/delete)',
   'user', 'dbo', 'table', 'FriendGroup'
go

/*==============================================================*/
/* Table: Friendship                                            */
/*==============================================================*/
create table dbo.Friendship (
   ID                   int                  not null,
   UserId               int                  not null,
   FriendId             int                  not null,
   CreateDate           datetime             not null,
   Confirmed            bit                  not null,
   ConfirmDate          datetime             null,
   GroupId              int                  not null,
   Note                 varchar(1024)        not null,
   constraint PK_FRIENDSHIP primary key (ID)
)
go

/*==============================================================*/
/* Table: History                                               */
/*==============================================================*/
create table dbo.History (
   ID                   int                  not null,
   UserId               int                  null,
   LogType              int                  not null,
   LogDate              datetime             not null,
   Message              varchar(4000)        not null,
   IPAddress            varchar(64)          null,
   ModuleId             int                  null,
   constraint PK_HISTORY primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Operating records',
   'user', 'dbo', 'table', 'History'
go

execute sp_addextendedproperty 'MS_Description', 
   'null for system event',
   'user', 'dbo', 'table', 'History', 'column', 'UserId'
go

execute sp_addextendedproperty 'MS_Description', 
   'It is a enumerator:
   1. Account log
   2. System event
   and so on',
   'user', 'dbo', 'table', 'History', 'column', 'LogType'
go

execute sp_addextendedproperty 'MS_Description', 
   'null for system event, compatible with IPv6',
   'user', 'dbo', 'table', 'History', 'column', 'IPAddress'
go

execute sp_addextendedproperty 'MS_Description', 
   'From which module, null for system event',
   'user', 'dbo', 'table', 'History', 'column', 'ModuleId'
go

/*==============================================================*/
/* Table: Message                                               */
/*==============================================================*/
create table dbo.Message (
   ID                   int                  not null,
   FromUserId           int                  not null,
   ToUserId             int                  not null,
   Subject              varchar(128)         not null,
   Message              varchar(2000)        not null,
   SentDate             datetime             not null,
   IsNew                bit                  not null,
   IsDeleted            tinyint              not null,
   Type                 tinyint              not null,
   constraint PK_MESSAGE primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Personal message',
   'user', 'dbo', 'table', 'Message'
go

execute sp_addextendedproperty 'MS_Description', 
   'just for sendee:
   0 - not new(read)
   1 - new(unread)',
   'user', 'dbo', 'table', 'Message', 'column', 'IsNew'
go

execute sp_addextendedproperty 'MS_Description', 
   'for user to user message
   0 - not yet delete
   1 - sendee delete
   2 - sender delete
   
   for system message
   0 - not yet delete
   1 - sendee delete',
   'user', 'dbo', 'table', 'Message', 'column', 'IsDeleted'
go

execute sp_addextendedproperty 'MS_Description', 
   '1 - user to user
   2 - system',
   'user', 'dbo', 'table', 'Message', 'column', 'Type'
go

/*==============================================================*/
/* Table: Module                                                */
/*==============================================================*/
create table dbo.Module (
   ID                   int                  identity,
   ModuleName           varchar(64)          not null,
   ModuleIcon           varchar(128)         not null,
   EntryPoint           varchar(64)          not null,
   DisplayName          varchar(64)          not null,
   Description          varchar(4000)        not null,
   Suppressible         bit                  not null,
   constraint PK_MODULE primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Module list (include the third-party module)',
   'user', 'dbo', 'table', 'Module'
go

execute sp_addextendedproperty 'MS_Description', 
   'For code and url',
   'user', 'dbo', 'table', 'Module', 'column', 'ModuleName'
go

execute sp_addextendedproperty 'MS_Description', 
   'Icon of module',
   'user', 'dbo', 'table', 'Module', 'column', 'ModuleIcon'
go

execute sp_addextendedproperty 'MS_Description', 
   'A relative link to the default page of this module',
   'user', 'dbo', 'table', 'Module', 'column', 'EntryPoint'
go

execute sp_addextendedproperty 'MS_Description', 
   'For UI',
   'user', 'dbo', 'table', 'Module', 'column', 'DisplayName'
go

execute sp_addextendedproperty 'MS_Description', 
   'Text or html format',
   'user', 'dbo', 'table', 'Module', 'column', 'Description'
go

execute sp_addextendedproperty 'MS_Description', 
   'Whether be able to hide, some module do not allow to hide',
   'user', 'dbo', 'table', 'Module', 'column', 'Suppressible'
go

/*==============================================================*/
/* Table: Profile                                               */
/*==============================================================*/
create table dbo.Profile (
   ID                   int                  not null,
   TrueName             varchar(64)          not null,
   NickName             varchar(64)          not null,
   Gender               varchar(8)           not null,
   Birthday             datetime             null,
   Hometown             varchar(128)         null,
   QQ                   varchar(128)         null,
   MSN                  varchar(128)         null,
   PhoneNumber          varchar(128)         null,
   Website              varchar(128)         null,
   State                varchar(128)         null,
   StateRefreshTime     datetime             null,
   Avatar               varchar(128)         not null,
   constraint PK_PROFILE primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Profile of users (incomplete)',
   'user', 'dbo', 'table', 'Profile'
go

execute sp_addextendedproperty 'MS_Description', 
   'UserId',
   'user', 'dbo', 'table', 'Profile', 'column', 'ID'
go

execute sp_addextendedproperty 'MS_Description', 
   'If empty, it will as same as truename',
   'user', 'dbo', 'table', 'Profile', 'column', 'NickName'
go

execute sp_addextendedproperty 'MS_Description', 
   'It is a enumerator
   Male, Female, Other, Unknown ',
   'user', 'dbo', 'table', 'Profile', 'column', 'Gender'
go

execute sp_addextendedproperty 'MS_Description', 
   'yyyyMMdd: date of birth
   3000MMdd: birthday without year',
   'user', 'dbo', 'table', 'Profile', 'column', 'Birthday'
go

execute sp_addextendedproperty 'MS_Description', 
   'Compatible with email login method for TM',
   'user', 'dbo', 'table', 'Profile', 'column', 'QQ'
go

execute sp_addextendedproperty 'MS_Description', 
   'Allow to input any characters for more than one phone number with separator',
   'user', 'dbo', 'table', 'Profile', 'column', 'PhoneNumber'
go

execute sp_addextendedproperty 'MS_Description', 
   'Personal web site or blog',
   'user', 'dbo', 'table', 'Profile', 'column', 'Website'
go

execute sp_addextendedproperty 'MS_Description', 
   'recent situation (state of user)',
   'user', 'dbo', 'table', 'Profile', 'column', 'State'
go

execute sp_addextendedproperty 'MS_Description', 
   'The logo of user, it should be a face',
   'user', 'dbo', 'table', 'Profile', 'column', 'Avatar'
go

/*==============================================================*/
/* Table: Resume                                                */
/*==============================================================*/
create table dbo.Resume (
   ID                   int                  identity,
   UserId               int                  not null,
   Type                 tinyint              not null,
   StartDate            datetime             not null,
   EndDate              datetime             not null,
   Place                varchar(128)         not null,
   Department           varchar(128)         not null,
   Position             varchar(128)         not null,
   constraint PK_RESUME primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Resume of users, education and work history',
   'user', 'dbo', 'table', 'Resume'
go

execute sp_addextendedproperty 'MS_Description', 
   'It is a enumerator:
   1. education
   2. work history',
   'user', 'dbo', 'table', 'Resume', 'column', 'Type'
go

execute sp_addextendedproperty 'MS_Description', 
   'Ignore the day',
   'user', 'dbo', 'table', 'Resume', 'column', 'StartDate'
go

execute sp_addextendedproperty 'MS_Description', 
   'Ignore the day (year=3000 if now)',
   'user', 'dbo', 'table', 'Resume', 'column', 'EndDate'
go

execute sp_addextendedproperty 'MS_Description', 
   'School or company',
   'user', 'dbo', 'table', 'Resume', 'column', 'Place'
go

execute sp_addextendedproperty 'MS_Description', 
   'Major or job department',
   'user', 'dbo', 'table', 'Resume', 'column', 'Department'
go

execute sp_addextendedproperty 'MS_Description', 
   'Degree or working position',
   'user', 'dbo', 'table', 'Resume', 'column', 'Position'
go

/*==============================================================*/
/* Table: UserModuleMap                                         */
/*==============================================================*/
create table dbo.UserModuleMap (
   ID                   int                  identity,
   UserId               int                  not null,
   ModuleId             int                  not null,
   AddDate              datetime             not null,
   Rank                 int                  not null,
   constraint PK_USERMODULEMAP primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Which modules did user selected to display in his default page',
   'user', 'dbo', 'table', 'UserModuleMap'
go

execute sp_addextendedproperty 'MS_Description', 
   'Used for sorting',
   'user', 'dbo', 'table', 'UserModuleMap', 'column', 'Rank'
go

/*==============================================================*/
/* Table: Users                                                 */
/*==============================================================*/
create table dbo.Users (
   ID                   int                  identity,
   Email                varchar(128)         not null,
   Password             varchar(32)          not null,
   Salt                 varchar(32)          not null,
   Status               tinyint              not null,
   Online               bit                  not null,
   CreateDate           datetime             not null,
   VerifyDate           datetime             null,
   LastLoginDate        datetime             not null,
   constraint PK_USERS primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Account information of users',
   'user', 'dbo', 'table', 'Users'
go

execute sp_addextendedproperty 'MS_Description', 
   'Used for login',
   'user', 'dbo', 'table', 'Users', 'column', 'Email'
go

execute sp_addextendedproperty 'MS_Description', 
   'Encrypted with salt',
   'user', 'dbo', 'table', 'Users', 'column', 'Password'
go

execute sp_addextendedproperty 'MS_Description', 
   'For password encrypt',
   'user', 'dbo', 'table', 'Users', 'column', 'Salt'
go

execute sp_addextendedproperty 'MS_Description', 
   'The status of user, it is a enumerator
   1. Email unvalidated
   2. Normal
   3. Locked
   4. Unenlivened (have not login for a long time)',
   'user', 'dbo', 'table', 'Users', 'column', 'Status'
go

execute sp_addextendedproperty 'MS_Description', 
   'Is online?',
   'user', 'dbo', 'table', 'Users', 'column', 'Online'
go

/*==============================================================*/
/* Table: VerifyCode                                            */
/*==============================================================*/
create table dbo.VerifyCode (
   ID                   int                  not null,
   UserId               int                  not null,
   CodeType             int                  not null,
   VerifyCode           varchar(128)         not null,
   CreateDate           datetime             not null,
   SendDate             datetime             null,
   ExpireDate           datetime             not null,
   constraint PK_VERIFYCODE primary key (ID)
)
go

execute sp_addextendedproperty 'MS_Description', 
   'Random code for email verification, password reset, invitation, and so on',
   'user', 'dbo', 'table', 'VerifyCode'
go

execute sp_addextendedproperty 'MS_Description', 
   'It is a enumerator:
   1. Email verification
   2. Password reset
   3. Invitation',
   'user', 'dbo', 'table', 'VerifyCode', 'column', 'CodeType'
go

alter table dbo.FriendGroup
   add constraint FK_FriendGrop_UserId_Users foreign key (UserId)
      references dbo.Users (ID)
go

alter table dbo.Friendship
   add constraint FK_Friendship_FriendId_Users foreign key (FriendId)
      references dbo.Users (ID)
go

alter table dbo.Friendship
   add constraint FK_Friendship_GroupId_FriendGroup foreign key (GroupId)
      references dbo.FriendGroup (ID)
go

alter table dbo.Friendship
   add constraint FK_Friendship_UserId_Users foreign key (UserId)
      references dbo.Users (ID)
go

alter table dbo.History
   add constraint FK_History_ModuleId_Module foreign key (ModuleId)
      references dbo.Module (ID)
go

alter table dbo.History
   add constraint FK_History_UserId_Users foreign key (UserId)
      references dbo.Users (ID)
go

alter table dbo.Message
   add constraint Reference_Message_FromUserId_Users foreign key (FromUserId)
      references dbo.Users (ID)
go

alter table dbo.Message
   add constraint Reference_Message_ToUserId_Users foreign key (ToUserId)
      references dbo.Users (ID)
go

alter table dbo.Profile
   add constraint FK_Profile_ID_Users foreign key (ID)
      references dbo.Users (ID)
go

alter table dbo.Resume
   add constraint FK_Resume_UserId_Users foreign key (UserId)
      references dbo.Users (ID)
go

alter table dbo.UserModuleMap
   add constraint FK_UseModuleMap_ModuleId_Module foreign key (ModuleId)
      references dbo.Module (ID)
go

alter table dbo.UserModuleMap
   add constraint FK_UseModuleMap_UserId_Users foreign key (UserId)
      references dbo.Users (ID)
go

alter table dbo.VerifyCode
   add constraint FK_VerifyCode_UserId_Users foreign key (UserId)
      references dbo.Users (ID)
go

